﻿using System;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Reflection;
using System.Xml.Serialization;
using System.Xml;
using System.Text;
using System.IO;
using MySqlX.XDevAPI.Relational;
using Mysqlx.Crud;
using Org.BouncyCastle.Asn1.X509;

public class MysqlFunction
{
    public static T FindInTable<T>(string database, SqlTable table, string key, string id) where T : class, new()
    {
        T res = null;

        MysqlFunction.SelectFromTable(database, table, key, id, (reader) =>
        {
            if (reader.HasRows)
            {
                T row = new T();
                MysqlFunction.ReadTable(reader, ref row);
                res = row;
            }
        });

        return res;
    }
    public static T FindInTable<T>(string database, SqlTable table, string key1, string value1, string key2, string value2) where T : class, new()
    {
        T res = null;

        MysqlFunction.SelectFromTable(database, table, key1, value1,key2,value2 ,(reader) =>
        {
            if (reader.HasRows)
            {
                T row = new T();
                MysqlFunction.ReadTable(reader, ref row);
                res = row;
            }
        });

        return res;
    }
    public static T[] FindRowsInTable<T>(string database, SqlTable table,string col, string value,int maxCount = int.MaxValue) where T : class, new()
    {
        T[] res = new T[0];
        MysqlFunction.SelectFromTable(database, table, col, value,
        (reader) =>
        {
            if (reader.HasRows)
            {
                T[] tables = null;
                MysqlFunction.ReadTable(reader, ref tables, table.ToString(),maxCount);
#if SomeLog
                GameDebug.Log($"table.ToString():{tables.GetArrStr()}");
#endif
                res = tables;
            }
        });

        return res;
    }

    public static T[] FindRowsWithContainsInTable<T>(string database, SqlTable table, string column, string target, int maxCount = int.MaxValue) where T : class, new()
    {
        T[] res = new T[0];
        string sqlInPark = string.Format(@"select * from {0} where `{1}` LIKE '%{2}%' LIMIT {3}", table, column, target, maxCount);
#if SomeLog
        GameDebug.Log("SelectFromTable sql:" + sqlInPark);
#endif
        MysqlHelper.ExecuteReader(database, sqlInPark, 
            (reader) =>
            {
                if (reader.HasRows)
                {
                    T[] tables = null;
                    MysqlFunction.ReadTable(reader, ref tables, table.ToString(), maxCount);
#if SomeLog
                GameDebug.Log($"table.ToString():{tables.GetArrStr()}");
#endif
                    res = tables;
                }
            });

        return res;
    }
    public static T[] SelectRowsInTable<T>(string database, SqlTable table, int maxCount) where T : class, new()
    {
        T[] res = new T[0];
        string sqlInPark = $"SELECT * FROM {table} LIMIT {maxCount}";
        MysqlHelper.ExecuteReader(database, sqlInPark, (reader) =>
        {
            if (reader.HasRows)
            {
                T[] tables = null;
                MysqlFunction.ReadTable(reader, ref tables, table.ToString());
                res = tables;
            }
        });
        return res;
    }
    public static T[] SelectRowsInTableDesc<T>(string database, SqlTable table, int maxCount) where T : class, new()
    {
        T[] res = new T[0];
        string sqlInPark = $"SELECT * FROM {table} ORDER BY id DESC LIMIT {maxCount}";
        MysqlHelper.ExecuteReader(database, sqlInPark, (reader) =>
        {
            if (reader.HasRows)
            {
                T[] tables = null;
                MysqlFunction.ReadTable(reader, ref tables, table.ToString());

                //GameDebug.Log($"SelectRowsInTableDesc ReadTable:{tables.GetArrStr()}");
                res = tables;
            }
        });
        return res;
    }
    public static T[] SelectRowsInTableDesc<T>(string database, SqlTable table,string whereCol,string whereValue, int maxCount) where T : class, new()
    {
        T[] res = new T[0];
        string sqlInPark = $"SELECT * FROM {table}  where {whereCol} = '{whereValue}' ORDER BY id DESC  LIMIT {maxCount}";
        MysqlHelper.ExecuteReader(database, sqlInPark, (reader) =>
        {
            if (reader.HasRows)
            {
                T[] tables = null;
                MysqlFunction.ReadTable(reader, ref tables, table.ToString());

                //GameDebug.Log($"SelectRowsInTableDesc ReadTable:{tables.GetArrStr()}");
                res = tables;
            }
        });
        return res;
    }
    public static void SelectFromTable(string database,SqlTable table, string column, string target,Action<MySqlDataReader> onEnd, int maxCount = int.MaxValue)
    {
        SelectFromTable(database,table.ToString(),column,target, onEnd, maxCount);
    }

    public static void SelectFromTable(string database, string table, string column, string target, Action<MySqlDataReader> onEnd, int maxCount = int.MaxValue)
    {
        string sqlInPark = string.Format(@"select * from {0} where {1} = '{2}' LIMIT {3}", table, column, target, maxCount);
#if SomeLog
        GameDebug.Log("SelectFromTable sql:" + sqlInPark);
#endif
        MysqlHelper.ExecuteReader(database,sqlInPark, onEnd);
    }

    public static void SelectFromTable(string database,SqlTable table, string column1, string target1, string column2, string target2, Action<MySqlDataReader> onEnd)
    {
        SelectFromTable(database,table.ToString(), column1, target1, column2, target2, onEnd);
    }

    public static void SelectFromTable(string database, string table, string column1, string target1, string column2, string target2, Action<MySqlDataReader>onEnd)
    {
        string sqlInPark = string.Format(@"select * from {0} where {1} = '{2}' and {3} = '{4}'", table, column1, target1, column2, target2);
#if SomeLog
        GameDebug.Log("SelectFromTable sql:" + sqlInPark);
#endif
        MysqlHelper.ExecuteReader(database,sqlInPark, onEnd);
    }
    public static int GetTableRowsCount(string database, string table)
    {
        return GetTableRowsCount(database, table.ToString());
    }

    public static int GetTableRowsCount(string database, SqlTable table)
    {
        string sqlQuery = string.Format(@"select * from {0}", table);
        var readerList = MysqlHelper.GetDataTable(database, sqlQuery);
        return readerList.Rows.Count;
    }

    public static long InsertIntoTable<T>(string database, SqlTable table,T tableObj,bool skipId = true)
    {
        var colunmeList = new List<string>();
        var valusList = new List<object>();
        foreach (FieldInfo field in tableObj.GetType().GetFields()) //读取对象的类中每个变量及对象对应的值
        {
            if (skipId && field.Name.Equals("id"))
                continue;

            //GameDebug.Log("col:" +field.Name + " value:"+ field.GetValue(tableObj));
            colunmeList.Add(field.Name);
            valusList.Add(field.GetValue(tableObj));
        }
        return InsertIntoTable(database, table.ToString(), colunmeList, valusList);
    }

    public static long InsertIntoTable(string database, string table, List<string> columeList, List<object> valusList)
    {
        string columeStr = "";
        foreach (var col in columeList)
        {
            columeStr += "`" + col + "`" + ",";
        }
        columeStr = RemoveLastComma(columeStr);
        string valueStr = "";
        foreach (var value in valusList)
        {
            valueStr += "'" + value + "'" + ",";
        }
        valueStr = RemoveLastComma(valueStr);
        string sqlInsert = string.Format("insert into {0}({1}) values({2})", table, columeStr, valueStr);
#if SomeLog
        GameDebug.Log("InsertIntoTable sql:" + sqlInsert+ "  columeStr :"+ columeStr + "  valueStr:" + valueStr);
#endif
        return MysqlHelper.ExecuteInsertSql(database, sqlInsert);
    }

    public static int UpdateValue(string database, string table,string colume,string value,string whereColume,string whereValue)
    {
        string sqlInsert = string.Format("UPDATE {0} SET {1} = \"{2}\" WHERE {3} = {4}", table, colume, value, whereColume, whereValue);
#if SomeLog
        GameDebug.Log("UpdateValue sql:" + sqlInsert);
#endif
        return MysqlHelper.ExecuteSql(database, sqlInsert);
    }
    public static int UpdateValue(string database, string table, string colume1, string value1,string colume2,string value2,string whereColume, string whereValue)
    {
        return UpdateValues(database, table,new string[] {colume1,colume2 },new string[] { value1,value2},new string[]{ whereColume},new string[] { whereValue });
    }

    public static int UpdateValueMultiWhere(string database, string table, string colume1, string value1, string whereColume1, string whereValue1, string whereColume2, string whereValue2)
    {
        return UpdateValues(database, table, new string[] { colume1 }, new string[] { value1 }, new string[] { whereColume1, whereColume2 }, new string[] { whereValue1, whereValue2 });
    }

    public static int UpdateValue(string database, string table, string colume1, string value1, string colume2, string value2, string whereColume1, string whereValue1, string whereColume2, string whereValue2)
    {
        return UpdateValues(database, table, new string[] { colume1, colume2 }, new string[] { value1, value2 }, new string[] { whereColume1, whereColume2 }, new string[] { whereValue1, whereValue2 });
    }

    public static int UpdateValues(string database, string table, string[] columes, string[] values, string[] whereColumes, string[] whereValues)
    {
        if(columes == null || values == null)
        {
            throw new System.ArgumentNullException($"columes == null:{columes == null}  values == null:{values == null}");
        }
        if(columes.Length != values.Length)
        {
            throw new System.ArgumentNullException($"columes.Length:{columes.Length} !=  values.Length:{values.Length}");
        }
        if(columes.Length <= 0)
        {
            throw new System.ArgumentNullException($"columes.Length <= 0");
        }

        if (whereColumes == null || whereValues == null)
        {
            throw new System.ArgumentNullException($"whereColumes == null:{whereColumes == null}  whereValues == null:{whereValues == null}");
        }
        if (whereColumes.Length != whereValues.Length)
        {
            throw new System.ArgumentNullException($"whereColumes.Length:{whereColumes.Length} !=  whereValues.Length:{whereValues.Length}");
        }
        if (whereColumes.Length <= 0)
        {
            throw new System.ArgumentNullException($"whereColumes.Length <= 0");
        }

        string setStr = "";
        for(int i=0;i< columes.Length; i++)
        {
            setStr += string.Format("{0} = \"{1}\"",columes[i],values[i]);
            if(i != columes.Length - 1)
            {
                setStr += ",";
            }
        }

        string whereStr = "";
        for (int i = 0; i < whereColumes.Length; i++)
        {
            whereStr += string.Format("{0} = \"{1}\"", whereColumes[i], whereValues[i]);
            if (i != whereColumes.Length - 1)
            {
                whereStr += " and ";
            }
        }

        string sqlInsert = string.Format("UPDATE  {0} SET {1} WHERE {2}", table, setStr, whereStr);
 #if SomeLog
        GameDebug.Log("UpdateValue sql:" + sqlInsert);
#endif

        return MysqlHelper.ExecuteSql(database, sqlInsert);
    }

    public static int DeleteRow(string database,string table,long id)
    {
        string sqlDelete = string.Format("DELETE FROM {0} WHERE id ={1}",table,id);
        GameDebug.Log("Delete sql:" + sqlDelete);

        return MysqlHelper.ExecuteSql(database, sqlDelete);
    }

    public static void ReadTable<T>( MySqlDataReader reader, ref T[] tableObjArr,string tableName = "",int maxCount = int.MaxValue)where T:class,new()
    {
        try
        {
            List<T> res = new List<T>();
            while (reader.Read())
            {
                var tableObj = new T();
                foreach (FieldInfo field in tableObj.GetType().GetFields())
                {
                    var fieldType = field.FieldType;
                    if (fieldType == typeof(int))
                    {
                        field.SetValue(tableObj, reader.GetInt32(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(ushort))
                    {
                        field.SetValue(tableObj, reader.GetUInt16(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(byte))
                    {
                        field.SetValue(tableObj, reader.GetByte(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(long))
                    {
                        field.SetValue(tableObj, reader.GetInt64(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(float))
                    {
                        field.SetValue(tableObj, reader.GetFloat(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(double))
                    {
                        field.SetValue(tableObj, reader.GetDouble(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(string))
                    {
                        field.SetValue(tableObj, reader.GetString(reader.GetOrdinal(field.Name)));
                    }
                    if (field.GetValue(tableObj) == null)
                    {
                        field.SetValue(tableObj, 0);
                    }
                    //GameDebug.Log($"ReadTable {tableName} field.FieldType:" + fieldType + "  value :" + field.GetValue(tableObj));

                }

                res.Add(tableObj);

                if (res.Count > maxCount)
                    break;
            }
            tableObjArr = res.ToArray();
        }
        catch(Exception e)
        {
            GameDebug.LogError($"{e.Message}" + "\n" + $"{e.StackTrace}");
        }
    }

    public static void ReadTable<T>( MySqlDataReader reader, ref T tableObj)
    {
        try
        {
            while (reader.Read())
            {
                foreach (FieldInfo field in tableObj.GetType().GetFields())
                {
                    var fieldType = field.FieldType;
                    if (fieldType == typeof(int))
                    {
                        field.SetValue(tableObj, reader.GetInt32(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(ushort))
                    {
                        field.SetValue(tableObj, reader.GetUInt16(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(byte))
                    {
                        field.SetValue(tableObj, reader.GetByte(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(long))
                    {
                        field.SetValue(tableObj, reader.GetInt64(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(float))
                    {
                        field.SetValue(tableObj, reader.GetFloat(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(double))
                    {
                        field.SetValue(tableObj, reader.GetDouble(reader.GetOrdinal(field.Name)));
                    }
                    else if (fieldType == typeof(string))
                    {
                        field.SetValue(tableObj, reader.GetString(reader.GetOrdinal(field.Name)));
                    }
                    if (field.GetValue(tableObj) == null)
                    {
                        field.SetValue(tableObj, 0);
                    }
                }
            }
        }
        catch (Exception e)
        {
            GameDebug.LogError($"{e.Message}" + "\n" + $"{e.StackTrace}");
        }
    }

    public static bool CreateDatabaseIfNotExist(string database)
    {
        string existSql = $"SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME = '{database}';";

        var dataSet =  MysqlHelper.GetDataSet(existSql);

        bool newCrewate = false;
        if (dataSet != null && dataSet.Tables[0].Rows.Count> 0)
        {
            GameDebug.Log($"数据库：{database}  已存在   dataSet.Tables[0].Rows.Count：{dataSet.Tables[0].Rows.Count}");
        }
        else
        {
            GameDebug.Log($"数据库：{database} 不存在，新创建   dataSet:{dataSet != null}  dataSet.Tables[0].Rows.Count:{dataSet?.Tables[0].Rows.Count}");
            newCrewate = true;

            string sql = string.Format("CREATE DATABASE if not exists {0} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;", database);
            GameDebug.Log("ExistDatabase sql:" + sql);

            MysqlHelper.ExecuteSql("mysql", sql);
        }
        return newCrewate;
    }

    /// <summary>
    /// 去除最后一个逗号
    /// </summary>
    /// <param name="str"></param>
    /// <returns></returns>
    public static string RemoveLastComma(string str)
    {
        if (str.Contains(","))
        {
            return str.Substring(0, str.LastIndexOf(','));
        }
        return str;
    }

    public static int[] String2IntArray(string str)
    {
        string[] numStr = str.Split(',');
        int[] res = new int[numStr.Length];
        for(int i = 0; i < numStr.Length; i++)
        {
            int num;
            int.TryParse(numStr[i], out num);
            res[i] = num;
        }
        return res;
    }

    public static string IntArray2String(int[] arr)
    {
        string res = "";

        for(int i = 0; i < arr.Length; i++)
        {
            if (i < arr.Length - 1)
                res += arr[i] + ",";
            else
                res += arr[i].ToString();
        }
        return res;
    }

    public static int[][] String2IntArrayArray(string str)
    {
        string[] ArrStr = str.Split(';');
        int[][] res = new int[ArrStr.Length][];
        for (int i = 0; i < ArrStr.Length; i++)
        {
            string[] numStr = ArrStr[i].Split(',');
            res[i] = new int[numStr.Length];
            for (int j = 0; j < numStr.Length; j++)
            {
                int num;
                int.TryParse(numStr[j], out num);
                res[i][j] = num;
            }
        }
        return res;

    }

    public static string IntArrayArray2String(int[][] arrs)
    {
        string res = "";
        
        for (int i = 0; i < arrs.Length; i++)
        {
            string cur = "";
            for (int j = 0; j < arrs[i].Length; j++)
            {
                cur += arrs[i][j].ToString();

                if (j < arrs[i].Length - 1)
                    cur +=  ",";
            }
            res += cur;

            if (i < arrs.Length - 1)
                res +=  ";";
        }

        return res;
    }
    
}
